package com.fs.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.fs.entity.Administrators;
import com.fs.entity.Materials;
import com.fs.entity.Reviewers;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author author
 * @since 2024-06-12
 */
@Mapper
public interface AdministratorsMapper extends BaseMapper<Administrators> {
    @Select("SELECT departmentid, COUNT(*) as studentcount FROM (SELECT departmentid FROM students WHERE credit >= #{x}) AS subquery GROUP BY departmentid")
    List<Map<String, Object>> selectThanInDep(@Param("x") Integer x);//查询院部完成人数

    @Select("SELECT   \n" +
            "    all_classes.classname,  \n" +
            "    COALESCE(student_counts.student_count, 0) AS studentcount  \n" +
            "FROM (  \n" +
            "    SELECT DISTINCT classname FROM students  \n" +
            ") AS all_classes  \n" +
            "LEFT JOIN (  \n" +
            "    SELECT classname, COUNT(*) AS student_count  \n" +
            "    FROM students  \n" +
            "    WHERE credit >= #{x}  \n" +
            "    GROUP BY classname  \n" +
            ") AS student_counts ON all_classes.classname = student_counts.classname  \n" +
            "ORDER BY all_classes.classname;")
    List<Map<String, Object>> selectThanInClass(@Param("x") Integer x);//查询班级完成情况
    List<Materials> CheckClassNameMaterials(@Param("classname") Integer classname);




}
